package com.css.fxfzypg.manage.disasterrisk.repository.impl;

import com.css.fxfzypg.manage.disasterrisk.repository.DisasterRiskReqository;
import com.css.fxfzypg.manage.disasterrisk.repository.entity.DisasterRiskEntity;
import com.css.fxfzypg.manage.disasterrisk.vo.DisasterRiskParams;
import com.css.fxfzypg.modules.surveytaskmanage.entity.FieldSurveyTaskEntity;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class DisasterRiskReqositoryImpl implements DisasterRiskReqository {

    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    @Transactional
    public boolean saveDisasterRisk(DisasterRiskEntity disasterRiskEntity) {
        String sql = "insert into ypg_disaster_risk values(:id, :taskCode, :province, :provinceId, :city,  :cityId, :county, :countyId, :disasterAddress, :disasterName," +
                       " :longitude, :latitude, :disasterType, :disasterDescribe, :file, :isSubmit, :delFlag, :createName, :createId, :createTime, :submitter, :submitterId, :submitTime, ST_GeometryFromText(:geom,4490)) ";
        SqlParameterSource source = new BeanPropertySqlParameterSource(disasterRiskEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public Map<String,Object> querySubmitOrNot(String submitFlag, int curPage, int pageSize, String userId) {
        try {
            StringBuilder sql = new StringBuilder("select * from ypg_disaster_risk where 1=1 and del_flag = '0' and create_id = '"+userId+"' and is_submit = '"+ submitFlag+"'");
            if(submitFlag.equals("0")){
                sql.append(" order by create_time desc");
            }else{
                sql.append(" order by submit_time desc");
            }
            int firstIndex=(curPage-1) * pageSize;
            int lastIndex=pageSize;
            StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
            Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DisasterRiskEntity.class)).size();
            List<DisasterRiskEntity> disasterRiskEntities = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(DisasterRiskEntity.class));
            Map<String,Object> map=new HashMap();
            map.put("total",total);
            map.put("rows",disasterRiskEntities);
            return map;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public boolean updateIsSubmit(Map<String,String> map) {
        String sql = " update ypg_disaster_risk set is_submit = '1', submitter = ?, submitter_id = ?,submit_time = ? where id = ?";
        return jdbcTemplate.update(sql,map.get("userName"),map.get("userId"), new Date(), map.get("id")) > 0;
    }

    @Override
    public DisasterRiskEntity queryData(String id) {
        String sql ="select * from ypg_disaster_risk where del_flag = '0' and id = ?";
        DisasterRiskEntity disasterRiskEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DisasterRiskEntity.class),id);
        return disasterRiskEntity;
    }

    @Override
    public boolean modifyData(DisasterRiskEntity disasterRiskEntity) {
        String sql = "update ypg_disaster_risk set disaster_address = :disasterAddress, disaster_name = :disasterName," +
                " longitude = :longitude, latitude = :latitude, disaster_type = :disasterType, disaster_describe = :disasterDescribe, file = :file, " +
                "is_submit=:isSubmit,submitter=:submitter,submitter_id=:submitterId,submit_time=:submitTime where id='"+disasterRiskEntity.getId()+"\'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(disasterRiskEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public Map<String, Object> queryDisasterData(DisasterRiskParams queryParams, int curPage, int pageSize){
        StringBuilder sql = new StringBuilder("select * from ypg_disaster_risk ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and  is_submit = '1' ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())){
            whereSql.append(" and province_id = '"+ queryParams.getProvinceId() +"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCityId())){
            whereSql.append(" and city_id = '"+ queryParams.getCityId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCountyId())){
            whereSql.append(" and county_id = '"+ queryParams.getCountyId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDisasterAddress())){
            whereSql.append(" and disaster_address like '%"+ queryParams.getDisasterAddress() +"%'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDisasterName())){
            whereSql.append(" and disaster_name like '%"+ queryParams.getDisasterName()+"%'");
        }
        String disasterTypes = queryParams.getDisasterType();
        if(!PlatformObjectUtils.isEmpty(disasterTypes)){
            StringBuilder stringBuilder = new StringBuilder("and (");
            for (String disasterType : disasterTypes.split(",")){
                stringBuilder.append(" disaster_type like '%");
                stringBuilder.append(disasterType);
                stringBuilder.append("%' ");
                stringBuilder.append("or");
            }
            String typeSql = stringBuilder.toString().trim().substring(0,stringBuilder.length()-3) +")";
            whereSql.append( typeSql );
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())){
            whereSql.append(" and submitter like '%"+ queryParams.getSubmitter() +"%'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getStartTime())){
            whereSql.append(" and submit_time >= '"+ queryParams.getStartTime()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getEndTime())){
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and submit_time <= '"+endTime+"\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by submit_time desc");
        int firstIndex=(curPage-1) * pageSize;
        int lastIndex=pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        sql.append(whereSql).append(orderSql);
        Integer total= jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DisasterRiskEntity.class)).size();
        List<DisasterRiskEntity> disasterRiskList = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(DisasterRiskEntity.class));
        Map<String,Object> map = new HashMap();
        map.put("total",total);
        map.put("rows",disasterRiskList);
        return map;
    }

    @Override
    public boolean deleteDisasterRisk(String id) {
        String sql = "update ypg_disaster_risk set del_flag = 1 where id =?";
        return jdbcTemplate.update(sql,id) > 0;
    }

    @Override
    public List<DisasterRiskEntity> queryDisasterDataMap() {
        String sql = "select * from ypg_disaster_risk where del_flag = '0' and  is_submit = '1'";
        List<DisasterRiskEntity> disasterRiskEntities = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DisasterRiskEntity.class));
        return disasterRiskEntities;
    }

    @Override
    public List<DisasterRiskEntity> queryDisaster(DisasterRiskParams queryParams) {
        StringBuilder sql = new StringBuilder("select * from ypg_disaster_risk ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and  is_submit = '1' ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())){
            whereSql.append(" and province_id = '"+ queryParams.getProvinceId() +"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCityId())){
            whereSql.append(" and city_id = '"+ queryParams.getCityId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCountyId())){
            whereSql.append(" and county_id = '"+ queryParams.getCountyId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDisasterAddress())){
            whereSql.append(" and disaster_address like '%"+ queryParams.getDisasterAddress() +"%'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDisasterName())){
            whereSql.append(" and disaster_name like '%"+ queryParams.getDisasterName()+"%'");
        }
        String disasterTypes = queryParams.getDisasterType();
        if(!PlatformObjectUtils.isEmpty(disasterTypes)){
            StringBuilder stringBuilder = new StringBuilder("and (");
            for (String disasterType : disasterTypes.split(",")){
                stringBuilder.append(" disaster_type like '%");
                stringBuilder.append(disasterType);
                stringBuilder.append("%' ");
                stringBuilder.append("or");
            }
            String typeSql = stringBuilder.toString().trim().substring(0,stringBuilder.length()-3) +")";
            whereSql.append( typeSql );
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())){
            whereSql.append(" and submitter like '%"+ queryParams.getSubmitter() +"%'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getStartTime())){
            whereSql.append(" and submit_time >= '"+ queryParams.getStartTime()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getEndTime())){
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and submit_time <= '"+endTime+"\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by submit_time desc");
        sql.append(whereSql).append(orderSql);
        List<DisasterRiskEntity> disasterRiskList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DisasterRiskEntity.class));
       return disasterRiskList;
    }

    @Override
    public Map<String, Object> queryDisasterDataByProvinceId(DisasterRiskParams queryParams, int curPage, int pageSize, String provinceId) {
        StringBuilder sql = new StringBuilder("select * from ypg_disaster_risk ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and  is_submit = '1'  and province_id = '"+provinceId+"' ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getCityId())){
            whereSql.append(" and city_id = '"+ queryParams.getCityId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCountyId())){
            whereSql.append(" and county_id = '"+ queryParams.getCountyId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDisasterAddress())){
            whereSql.append(" and disaster_address like '%"+ queryParams.getDisasterAddress() +"%'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDisasterName())){
            whereSql.append(" and disaster_name like '%"+ queryParams.getDisasterName()+"%'");
        }
        String disasterTypes = queryParams.getDisasterType();
        if(!PlatformObjectUtils.isEmpty(disasterTypes)){
            StringBuilder stringBuilder = new StringBuilder("and (");
            for (String disasterType : disasterTypes.split(",")){
                stringBuilder.append(" disaster_type like '%");
                stringBuilder.append(disasterType);
                stringBuilder.append("%' ");
                stringBuilder.append("or");
            }
            String typeSql = stringBuilder.toString().trim().substring(0,stringBuilder.length()-3) +")";
            whereSql.append( typeSql );
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())){
            whereSql.append(" and submitter like '%"+ queryParams.getSubmitter() +"%'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getStartTime())){
            whereSql.append(" and submit_time >= '"+ queryParams.getStartTime()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getEndTime())){
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and submit_time <= '"+endTime+"\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by submit_time desc");
        int firstIndex=(curPage-1) * pageSize;
        int lastIndex=pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        sql.append(whereSql).append(orderSql);
        Integer total= jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DisasterRiskEntity.class)).size();
        List<DisasterRiskEntity> disasterRiskList = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(DisasterRiskEntity.class));
        Map<String,Object> map = new HashMap();
        map.put("total",total);
        map.put("rows",disasterRiskList);
        return map;
    }

    @Override
    public List<DisasterRiskEntity> queryDisasterDataByProvinceId(DisasterRiskParams queryParams, String provinceId) {
        StringBuilder sql = new StringBuilder("select * from ypg_disaster_risk ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and  is_submit = '1'  and province_id = '"+provinceId+"' ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getCityId())){
            whereSql.append(" and city_id = '"+ queryParams.getCityId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCountyId())){
            whereSql.append(" and county_id = '"+ queryParams.getCountyId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDisasterAddress())){
            whereSql.append(" and disaster_address like '%"+ queryParams.getDisasterAddress() +"%'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDisasterName())){
            whereSql.append(" and disaster_name like '%"+ queryParams.getDisasterName()+"%'");
        }
        String disasterTypes = queryParams.getDisasterType();
        if(!PlatformObjectUtils.isEmpty(disasterTypes)){
            StringBuilder stringBuilder = new StringBuilder("and (");
            for (String disasterType : disasterTypes.split(",")){
                stringBuilder.append(" disaster_type like '%");
                stringBuilder.append(disasterType);
                stringBuilder.append("%' ");
                stringBuilder.append("or");
            }
            String typeSql = stringBuilder.toString().trim().substring(0,stringBuilder.length()-3) +")";
            whereSql.append( typeSql );
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())){
            whereSql.append(" and submitter like '%"+ queryParams.getSubmitter() +"%'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getStartTime())){
            whereSql.append(" and submit_time >= '"+ queryParams.getStartTime()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getEndTime())){
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and submit_time <= '"+endTime+"\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by submit_time desc");
        sql.append(whereSql).append(orderSql);
        List<DisasterRiskEntity> disasterRiskList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DisasterRiskEntity.class));
        return disasterRiskList;
    }

    @Override
    public List<DisasterRiskEntity> queryDisasterDataMapByProvinceId(String provinceId) {
        String sql = "select * from ypg_disaster_risk where del_flag = '0' and  is_submit = '1' and province_id ='"+provinceId+"'";
        List<DisasterRiskEntity> disasterRiskEntities = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DisasterRiskEntity.class));
        return disasterRiskEntities;
    }
}
